Imports System.Windows.Forms
Public Class ExportarGrillaExcel
    Private Shared strTitle As String = "INPE - SIPGA"
    Private Shared strLogo As String = Application.StartupPath + "\BienServicio\ControlPatrimonial\Reportes\logo_inpe.gif"

    Public Shared Sub Exportar(ByVal vdatagridview As DataGridView)
        Dim sfd As SaveFileDialog = New SaveFileDialog()
        sfd.Filter = "Solo Excel (*.xls)|*.xls"
        If (sfd.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter(sfd.FileName)

            sw.WriteLine("<?xml version='1.0'?>")
            sw.WriteLine("<?mso-application progid='Excel.Sheet'?>")
            sw.WriteLine("<ss:Workbook xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>")
            sw.WriteLine("    <ss:Styles>")
            sw.WriteLine("        <ss:Style ss:ID='1'>")
            sw.WriteLine("           <ss:Font ss:Bold='1' ss:Color='#FFFFFF'/>")
            'sw.WriteLine("          <ss:Interior ss:Color='#333399' ss:Pattern='Solid'/>")
            sw.WriteLine("        </ss:Style>")
            sw.WriteLine("  <ss:Style ss:ID='s2'>")
            sw.WriteLine(" <ss:Alignment ss:Vertical='Bottom'/>")
            sw.WriteLine(" <ss:Borders>")
            sw.WriteLine(" <ss:Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>")
            sw.WriteLine(" <ss:Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>")
            sw.WriteLine(" <ss:Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>")
            sw.WriteLine(" <ss:Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>")
            sw.WriteLine(" </ss:Borders>")
            sw.WriteLine(" <ss:Font ss:Size='9'/>")
            sw.WriteLine(" <ss:Interior/>")
            sw.WriteLine(" <ss:NumberFormat/>")
            sw.WriteLine(" <ss:Protection/>")
            sw.WriteLine(" </ss:Style>")

            sw.WriteLine("          <ss:Style ss:ID='s23'>")
            sw.WriteLine("          <ss:Alignment ss:Vertical='Bottom'/>")
            sw.WriteLine("          <ss:Borders/>")
            sw.WriteLine("          <ss:Font ss:Color='#FFFFFF' ss:Bold='1'/>")
            sw.WriteLine("          <ss:Interior ss:Color='#333399' ss:Pattern='Solid'/>")
            sw.WriteLine("          <ss:NumberFormat/>")
            sw.WriteLine("          <ss:Protection/>")
            sw.WriteLine("      </ss:Style>")
            sw.WriteLine("    </ss:Styles>")





            sw.WriteLine("    <ss:Worksheet ss:Name='Sheet1'>")
            sw.WriteLine("        <ss:Table>")
            For Each Ctrl As DataGridViewColumn In vdatagridview.Columns
                If CStr(IIf(Ctrl.Tag Is Nothing, "", Ctrl.Tag)).Trim <> "1" Then
                    If Ctrl.Visible Then
                        sw.WriteLine("<ss:Column ss:Width='{0}'/>", Ctrl.Width)
                        System.Console.WriteLine(vdatagridview.Name.ToString)
                    End If
                End If
            Next
            sw.WriteLine("<ss:Row ss:StyleID='1'>")
            For Each Ctrl As DataGridViewColumn In vdatagridview.Columns
                If CStr(IIf(Ctrl.Tag Is Nothing, "", Ctrl.Tag)).Trim <> "1" Then
                    If Ctrl.Visible Then
                        'sw.WriteLine("<Cell ss:StyleID='s23'>")
                        sw.WriteLine("<ss:Cell ss:StyleID='s23'>")
                        sw.WriteLine(String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", Ctrl.HeaderText))
                        sw.WriteLine("</ss:Cell>")
                        'sw.WriteLine("</Cell>")
                    End If
                End If


            Next
            sw.WriteLine("</ss:Row ss:AutoFitHeight='0' ss:Height='21.9375'>")
            For intFila As Integer = 0 To vdatagridview.RowCount - 1
                sw.WriteLine(String.Format("<ss:Row ss:Height ='{0}'>", vdatagridview.Rows(intFila).Height))
                For Each Ctrl As DataGridViewColumn In vdatagridview.Columns
                    If CStr(IIf(Ctrl.Tag Is Nothing, "", Ctrl.Tag)).Trim <> "1" Then
                        If Ctrl.Visible Then
                            sw.WriteLine("<ss:Cell ss:StyleID='s2'>")
                            'sw.WriteLine(String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString()))
                            Dim strTag As String = ""

                            If Ctrl.Tag Is Nothing = True Then
                                strTag = ""
                            Else
                                strTag = Ctrl.Tag.ToString.Trim.ToLower
                            End If

                            Select Case strTag
                                Case "fecha"
                                    Dim strvalor As String = String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                                    If strvalor = String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", "0").ToString Then
                                        sw.WriteLine("")
                                    Else
                                        sw.WriteLine(String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", System.DateTime.FromFileTime(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value).ToShortDateString))
                                    End If
                                Case "numero"
                                    sw.WriteLine(String.Format("<ss:Data ss:Type='Number'>{0}</ss:Data>", vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString))
                                Case "check", "image"

                                Case Else
                                    sw.WriteLine(String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString))
                            End Select

                            sw.WriteLine("</ss:Cell>")
                        End If

                    End If
                Next
                sw.WriteLine("</ss:Row>")
            Next
            sw.WriteLine("</ss:Table>")
            sw.WriteLine("</ss:Worksheet>")
            sw.WriteLine("</ss:Workbook>")
            sw.Close()
            MessageBox.Show("Informacion Exportada Satisfactoriamente !!!", strTitle, MessageBoxButtons.OK, MessageBoxIcon.Information)

            Dim pr As New Process
            Try
                pr.StartInfo.FileName = sfd.FileName
                pr.Start()
            Catch ex As Exception
            End Try

        End If
    End Sub
    Public Shared Sub ExportarHTML(ByVal vdatagridview As DataGridView, Optional ByVal Titulo As String = "", _
    Optional ByVal Dependencia As String = "", Optional ByVal Usuario As String = "")
        Dim sfd As SaveFileDialog = New SaveFileDialog()
        sfd.Filter = "Solo Excel (*.xls)|*.xls"
        Dim wordInter As New WordInterpreter

        If (sfd.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter(sfd.FileName)
            sw.WriteLine("<%response.ContentType='application/vnd.ms-excel'%>")
            sw.WriteLine("<html>")
            sw.WriteLine("<head>")
            sw.WriteLine("<title></title>")
            sw.WriteLine("<style type='text/css'>")
            sw.WriteLine("<!--")
            sw.WriteLine(".style3 {color:white; font-weight: bold;font-size: 12px}")
            sw.WriteLine("  .style4 {font-size: 12px}")
            sw.WriteLine(".style2 {")
            sw.WriteLine("font-size: 21px;")
            sw.WriteLine("font-weight: bold;")
            sw.WriteLine("}")
            sw.WriteLine(".style5 {")
            sw.WriteLine("font-size: 17px;")
            sw.WriteLine("font-weight: bold;")
            sw.WriteLine("}")
            sw.WriteLine("-->")
            sw.WriteLine("</style>")
            sw.WriteLine("</head>")
            sw.WriteLine("<body>")
            sw.WriteLine("<table border='1'>")

            Dim i As Integer = 0
            For Each Ctrl As DataGridViewColumn In vdatagridview.Columns
                If Ctrl.Visible Then
                    i = i + 1
                End If
            Next
            '<tr height='20'>
            '<td border=0 colspan='15' align='center' valign='middle'><div align="right"><img src="logo.jpg" width="104" height="46" align="left">Fecha Generacion de Reporte :17/09/2008 02:29:24 p.m.</div></td>
            '</tr>
            sw.WriteLine("<tr height='46' border=0>")

            'sw.WriteLine("<td width='155'></td>")
            sw.WriteLine("<td border=0 colspan='" + (i).ToString + "' align='center' valign='middle'><div align='right'><img src='" + strLogo + "' width='250' height='46' align='left'>" + Now.ToString + "</td>")
            sw.WriteLine("</tr>")



            sw.WriteLine("<tr height='30' border=0>")
            'sw.WriteLine("<td width='155'></td>")
            sw.WriteLine("<td border=0 colspan='" + (i).ToString + "' align='center' valign='middle'><p class='style2'>Instituto Nacional Penitenciario</p></td>")
            sw.WriteLine("</tr>")
            sw.WriteLine("<tr height='20' border=0>")
            'sw.WriteLine("<td width='155'></td>")
            Dim Tit As String = ""
            Tit = wordInter.Interpretar(Titulo)
            sw.WriteLine("<td border=0 colspan='" + (i).ToString + "' align='center' valign='middle'><p class='style5'>" + Tit + "</p></td>")
            sw.WriteLine("</tr>")

            sw.WriteLine("<tr height='20' border=0>")
            'sw.WriteLine("<td width='155'></td>")
            Dim ofi As String = ""
            Dim usu As String = ""
            ofi = wordInter.Interpretar(Dependencia)
            usu = wordInter.Interpretar(Usuario)
            sw.WriteLine("<td border=0 colspan='" + (i).ToString + "' align='left' valign='middle'>Reporte generado por [ Usuario :" + usu + " ]&nbsp;&nbsp;[ Oficina :" + ofi + " ]</td>")
            sw.WriteLine("</tr>")
            'sw.WriteLine("<tr height='20' border=0>")
            ''sw.WriteLine("<td width='155'></td>")
            'usu = wordInter.Interpretar(vdatagridview.Usuario)
            'sw.WriteLine("<td border=0 colspan='" + (i).ToString + "' align='center' valign='middle'>" + usu + "</td>")
            'sw.WriteLine("</tr>")
            'sw.WriteLine("<tr height='20'>")
            ''sw.WriteLine("<td width='155'></td>")
            'sw.WriteLine("<td border=0 colspan='" + (i).ToString + "' align='center' valign='middle'>Fecha Generacion de Reporte :" + Now.ToString + "</td>")
            'sw.WriteLine("</tr>")
            'sw.WriteLine("<tr>")
            For Each Ctrl As DataGridViewColumn In vdatagridview.Columns
                If Ctrl.Visible Then
                    sw.WriteLine("<td width='" + Ctrl.Width.ToString + "' height='23' bgcolor=#333399><span class='style3'>" + wordInter.Interpretar(Ctrl.HeaderText) + "</span></td>")
                End If
            Next
            sw.WriteLine("</tr>")


            For intFila As Integer = 0 To vdatagridview.RowCount - 1
                sw.WriteLine("<tr>")
                For Each Ctrl As DataGridViewColumn In vdatagridview.Columns
                    If Ctrl.Visible And Ctrl.DividerWidth = 0 Then
                        sw.WriteLine("<td  width='" + Ctrl.Width.ToString + "' height='23'><span class='style4'>")
                        'sw.WriteLine(String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString()))

                        Select Case Ctrl.Tag.ToString.Trim.ToLower
                            Case "fecha"
                                Dim strvalor As String = String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                                If strvalor = String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", "0").ToString Then
                                    sw.WriteLine("")
                                Else
                                    sw.WriteLine(String.Format("<ss:Data ss:Type='String'>{0}</ss:Data>", System.DateTime.FromFileTime(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value).ToShortDateString))
                                End If
                            Case "numero"
                                sw.WriteLine(String.Format("<ss:Data ss:Type='Number'>{0}</ss:Data>", vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString))
                            Case "check"

                            Case Else
                                Dim s As String = ""
                                If IsNumeric(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString) Then
                                    Dim f1 As String
                                    f1 = vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString
                                    If f1.Length > 7 Then
                                        If f1.IndexOf(".") > 0 Then
                                            s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                                            sw.WriteLine(s)
                                        ElseIf f1.IndexOf(",") > 0 Then
                                            s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                                            sw.WriteLine(s)
                                        Else
                                            s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                                            s = "'" + s
                                            sw.WriteLine(s)
                                        End If

                                    Else
                                        s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                                        sw.WriteLine(s)
                                    End If
                                Else
                                    s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                                    sw.WriteLine(s)
                                End If
                        End Select

                        'If Ctrl.Tag = "Fecha" Then
                        '    If vdatagridview.Item(Ctrl.Name.ToString, intFila).Value = "0" Then
                        '        sw.WriteLine("")
                        '    Else
                        '        sw.WriteLine(System.DateTime.FromFileTime(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value).ToShortDateString)
                        '    End If

                        'Else
                        '    Dim s As String = ""
                        '    If IsNumeric(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString) Then
                        '        Dim f1 As String
                        '        f1 = vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString
                        '        If f1.Length > 7 Then
                        '            If f1.IndexOf(".") > 0 Then
                        '                s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                        '                sw.WriteLine(s)
                        '            ElseIf f1.IndexOf(",") > 0 Then
                        '                s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                        '                sw.WriteLine(s)
                        '            Else
                        '                s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                        '                s = "'" + s
                        '                sw.WriteLine(s)
                        '            End If

                        '        Else
                        '            s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                        '            sw.WriteLine(s)
                        '        End If
                        '    Else
                        '        s = wordInter.Interpretar(vdatagridview.Item(Ctrl.Name.ToString, intFila).Value.ToString)
                        '        sw.WriteLine(s)
                        '    End If
                        'End If
                        sw.WriteLine("</span></td>")
                    End If
                Next
                sw.WriteLine("</tr>")
            Next
            sw.WriteLine("</table>")
            sw.WriteLine("</body>")
            sw.WriteLine("</html>")

            sw.Close()
            MessageBox.Show("Informacion Exportada Satisfactoriamente...", strTitle, MessageBoxButtons.OK, MessageBoxIcon.Information)

            Dim pr As New Process
            Try
                pr.StartInfo.FileName = sfd.FileName
                pr.Start()
            Catch ex As Exception
            End Try
        End If
    End Sub

End Class
